import sys

import cx_Oracle
import pandas as pd
import os


"""
使用外部表+mege的方式进行 千万级别的数据量导入 实现主键判断 存在则更新 不存在则插入数据

"""
# 步骤1 建立外部表 并且使用已经存在的文本进行初始化

create_table_sql = """create table account_ext(id number parimary key,
                      name varchar2(10) not null,
                      iphone char2(11) unique,
                      valdat  date) organization external (
                      TYPE ORACLE_LOADER
                      DEFAULT DIRECTORY EXT_DIR
                      ACCESS PARAMETERS(
                      RECORDS DELIMITED BY NEWLINE
                      FIELDS TERMINATED BY ','
                      MISSING FIELDS BY NULL
                      )
                      LOCATION('fruit.csv')
                      );
                   """

# 1.连接数据库
def connectOracle():
    try:
        dsn = cx_Oracle.makedsn("127.0.0.1", 1521, service_name="orcl")
        conn = cx_Oracle.connect("scott","123456", dsn=dsn , encoding="utf-8")
    except Exception as ex:
        print(ex)
    print("Connected to Oracle")
    return conn

# 解析ACTVHH文本文件
def parse_ACTVHH_text_file(path):
    # 保存文件中的数据，records中保存可以直接插入数据表中的数据
    records = []
    # 读取文件内容
    with open(path, 'r', encoding='utf-8') as f:
        lines = f.readlines()
    # 处理每行数据
    for line in lines:
        # 跳过空行
        if not line.strip():
            continue
        #跳过开头和结尾

        # 检查是否是数据行（包含^ENDROW）
        if "^ENDROW" in line:
            record = line.split("|")
            record[0:1] = record[-1:] = []
            # print(len(record))
            records.append(record)
    # 类型转化

    print(f"✅ 成功解析 {len(records)} 条ACTVHH记录")

    return records

# def main():

    # # 1 连接数据库
    # conector = connectOracle()
    # # 创建游标 执行sql语句
    # cursor = conector.cursor()
    #
    # # cursor.execute("select * from RETTXN")
    # # result = cursor.fetchone()
    # # print("\n🔍 测试查询结果:", result)
    # records_ACTVHH = parse_ACTVHH_text_file("E:\测试数据\SBS3.ACTVHH-test-b.txt")
    # for record in records_ACTVHH:
    #     print(record)
    # # 执行插入语句到wide表中
    # sql ="""INSERT INTO WIDE(ORGNO) values (:1)"""
    # # cursor.executemany(sql, records_ACTVHH[0][0])
    # # print(records_ACTVHH[0][0])
    # test = []
    # # for record in records_ACTVHH:
    # #     test.append(record[0])
    # #     print(test)
    # test = [(record[0]) for record in records_ACTVHH]
    # print(test)
    # # cursor.executemany(sql,test)
    # cursor.execute("INSERT INTO WIDE (ORGNO, ACCNO) VALUES (:1, :2)", ('124', '000000'))
    # conector.commit()
    # print("✅ 单条测试插入成功")
    # return
def main():
#1、 连接数据库 数据库的位置【ip地址 端口号】连接数据库用户
    dsn01 = cx_Oracle.makedsn("127.0.0.1", 1521, service_name ="orcl")
    try:
        connector = cx_Oracle.connect(user="scott", password="123456", dsn=dsn01, encoding="utf-8")
    except cx_Oracle.Error as exc:
       exc_obj, = exc.args
       print("ORA 错误码：",exc_obj.code)
       print("ORA 错误详细信息：",exc_obj.message)
       sys.exit(1)
    else:
        print("Connected to Oracle")
    finally:
         print("进行完成第一步。。。。")

#2、 创建游标 执行sql语句

    # 编写merge sql语句
    mergesql = """merge """

    cursor = connector.cursor()

# 3 关闭连接




if __name__ == '__main__':
    main()